package com.ieds.modules.common.elasticsearch;

import com.ieds.modules.common.Util.StringUtil;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;


public class ExcelXFileTextExtractor {
    /**
     * 创建log对象
     */
    private static final Logger log = Logger.getLogger(ExcelXFileTextExtractor.class);

    private final static String xls = ".xls";
    private final static String xlsx = ".xlsx";

    public static String getText(File file) throws IOException {
        StringBuffer content = new StringBuffer();
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            if (in.available() / 1048576 > 3) {
                return "";
            }

            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file);

            if (workbook != null) {
                for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if(sheet == null){
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum  = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //循环除了第一行的所有行
                    for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if(row == null){
                            continue;
                        }
                        //获得当前行的开始列
                        int firstCellNum = row.getFirstCellNum();
                        //获得当前行的列数
                        int lastCellNum = row.getLastCellNum();

                        //循环当前行
                        for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                            Cell cell = row.getCell(cellNum);
                            content.append(getCellValue(cell));
                        }
                    }
                }
                workbook.close();
            }
        } catch (IOException e) {
            log.error("getText() 失败！", e);
        } finally {
            if (in != null ) {
                in.close();
            }
        }
        return StringUtil.washString(content.toString());
    }

    public static Workbook getWorkBook(File file) throws IOException {
        //获得文件名
        String fileName = file.getName();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        InputStream is = null;
        try {
            //获取excel文件的io流
            is = new FileInputStream(file);
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.getMessage();
        } finally {
            if (is != null) {
                is.close();
            }
        }
        return workbook;
    }

    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        CellType celltype = cell.getCellTypeEnum();
        if (celltype == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }

        //判断数据的类型
        switch (cell.getCellTypeEnum()){
            //数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "";
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /*public static void main(String[] args) throws IOException {
        File file = new File("E:\\ceshi\\ceshi.xls");
        ExcelXFileTextExtractor.getText(file);
    }*/
}
